﻿CREATE PROCEDURE [dbo].[usp_Trans_QuoteInsert]
@Salesman nvarchar(50), 
@FirstName nvarchar(50), 
@LastName nvarchar(50), 
@BoatType nvarchar(50), 
@BoatName nvarchar(50), 
@WorksOrderNumber nvarchar(50), 
@LOA int, 
@DeliveryWeek int, 
@DateCreated nvarchar(50), 
@MarketID int, 
@I real, 
@J float, 
@P float, 
@E float,  
@CurrencyID int, 
@BoatStyleID int, 
@Address1 nvarchar(50), 
@Address2 nvarchar(50), 
@Address3 nvarchar(50), 
@PostCode nvarchar(50), 
@City nvarchar(50), 
@CountryID int, 
@DeliveryMonth int, 
@UpdateDate nvarchar(50),
@Notes ntext,
@Telephone nvarchar(50),
@Fax nvarchar(50),
@Email nvarchar(50),
@VAT float,
@OrderID int,
@TotalValue float,
@OfferedValue float,
@PriceListItem bit,  
@CookieID int, 
@CustomerID int,
@LocalHostQuoteID  int,
@Fixed bit,
@FixedOfferValue float,
@ShippingCosts float,
@RefSalesman nvarchar(50),
@OEM bit,
@BoatGenID int, 
@DeliveryDate nvarchar(20),
@WorksOrderNumber2 nvarchar(20),
@StandardSailQuote bit,
@ISp float,
@Spl float,
@Ey float,
@Py float,
@PrivateCustomer bit,
@Stl float,
@NewDeliveryAddress bit,
@DealerComment nvarchar(250),
@MeasurementID int,
@DelDate datetime,
@DateCreate datetime,
@DateUpdated datetime,
@QuoteID int = null OUTPUT, 
@LineItem int, 
@QuoteSailTypeID int, 
@Luff float, 
@LporFoot float, 
@InputArea float, 
@GenericYarnID int, 
@BoatSpeed float, 
@AWS int, 
@FilmTypeID int,
@DPIID int,
@Discount float, 
@TierID int, 
@UsageID int, 
@CreateDate nvarchar(50), 
@ConstructionID int, 
@Price float, 
@SailModelCode nvarchar(50), 
@Cut nvarchar(50), 
@Weight nvarchar(50), 
@Multiple int,
@TotalPrice float,
@TotalOfferedPrice float,
@AccessoryID int,
@PreDiscountPrice float,
@SailNumber nvarchar(50),
@SailNumberColourID int,
@AccessoryColourID float,
@NylonColourDetails int,
@FixedOfferPrice float,
@BRP float,
@ExRate float,
@FabricID int,
@NewBRP float,
@NotionalArea bit, 
@AccessAttachLineitem int,
@TopSurfaceID int,
@BotSurfaceID int,
@AccessoryItemID int,
@DKKPrice float,
@MarketPriceEuro float,
@MarketRetailPRice float,
@SeasonalPriceEuro float,
@SeasonalPriceCurr float,
@OptionTotal float,
@BillingPrice float,
@BillingDiscount float,
@BillingCurrency nvarchar(50),
@OEMSailListID int,
@OriginatingOEMSailListID int,
@COM float,
@DesignFile nvarchar(50),
@AXItem nvarchar(50),
@NotArea float,
@DepartmentID int,
@LocaleID int,
@AttachedToLineItem int,
@HasAttachment bit,
@PreviousQuoteLineItemID int,
@SailMeasurementID int,
@OfferIncVat bit,
@ConvertToOrder bit,
@CutOnly bit,
@MembraneOnly bit,
@error nvarchar(4000) = null OUTPUT,
@QuoteLineItemID int OUTPUT
AS

	SET NOCOUNT ON;

BEGIN
	BEGIN TRY
		BEGIN TRANSACTION
			
			INSERT INTO Quote_Tbl (Salesman, FirstName, LastName, BoatType, BoatName, WorksOrderNumber, 
LOA, DeliveryWeek, DateCreated, MarketID, I, J, P, E,  CurrencyID, BoatStyleID, Address1, Address2, 
Address3, PostCode, City, CountryID, DeliveryMonth, UpdateDate, Notes, Telephone, Email, Fax, VAT, OrderID, 
TotalValue, OfferedValue, PriceListItem, CookieID, CustomerID, LocalHostQuoteID, Fixed, FixedOfferValue, 
ShippingCosts, RefSalesMan, OEM,  BoatGenID, DeliveryDate, WorksOrderNumber2, StandardSailQuote, ISP, 
Spl, Py, Ey, PrivateCustomer, Stl, NewDeliveryAddress, DealerComment, MeasurementID, DelDate, DateCreate, 
DateUpdated,  CutOnly, MembraneOnly)
VALUES 
(@Salesman, @FirstName, @LastName, @BoatType, @BoatName, @WorksOrderNumber, @LOA, @DeliveryWeek, 
@DateCreated, @MarketID, @I, @J, @P, @E,  @CurrencyID, @BoatStyleID, @Address1, @Address2, @Address3,
 @PostCode, @City, @CountryID, @DeliveryMonth, @UpdateDate, @Notes, @Telephone, @Email, @Fax, @VAT, @OrderID, 
@TotalValue, @OfferedValue, @PriceListItem, @CookieID, @CustomerID, @LocalHostQuoteID, @Fixed, @FixedOfferValue,
 @ShippingCosts, @RefSalesMan, @OEM,  @BoatGenID, @DeliveryDate, @WorksOrderNumber2, @StandardSailQuote, @ISP, 
@Spl, @Py, @Ey, @PrivateCustomer, @Stl, @NewDeliveryAddress, @DealerComment, @MeasurementID, @DelDate, @DateCreate, 
@DateUpdated, @CutOnly, @MembraneOnly)

			
			
			SET @QuoteID=(SELECT QuoteID FROM Quote_Tbl WHERE DateCreated = @DateCreated)

			INSERT INTO QuoteLineItem_Tbl (QuoteID, LineItem, QuoteSailTypeID, Luff, LporFoot, 
InputArea, GenericYarnID, BoatSpeed, AWS, FilmTypeID, DPIID, CurrencyID, Discount, TierID, UsageID, 
CreateDate, ConstructionID, Price, SailModelCode, Cut, Weight, Multiple, TotalPrice, TotalOfferedPrice, 
AccessoryID, PreDiscountPrice, SailNumber, SailNumberColourID, AccessoryColourID, NylonColourDetails, 
FixedOfferPrice, BRP, ExRate, FabricID, NewBRP, NotionalArea, AccessAttachLineItem, TopSurfaceID, BotSurfaceID, 
AccessoryItemID, DKKPrice, MarketPriceEuro, MarketRetailPrice, SeasonalPriceEuro, SeasonalPriceCurr, 
OptionTotal, BillingPrice, BillingDiscount, BillingCurrency, OEMSailListID, OriginatingOEMSailListID, COM, 
DesignFile, AxItem, Notes, NotArea, DeliveryDate, DepartmentID, LocaleID, AttachedToLineItem, HasAttachment, 
PreviousQuoteLineItemID, SailMeasurementID, OfferIncVat, ConvertToOrder) 
VALUES
 (@QuoteID, @LineItem, @QuoteSailTypeID, @Luff, @LporFoot, @InputArea, @GenericYarnID, @BoatSpeed, @AWS, 
@FilmTypeID, @DPIID, @CurrencyID, @Discount, @TierID, @UsageID, @CreateDate, @ConstructionID, @Price, @SailModelCode, 
@Cut, @Weight,  @Multiple, @TotalPrice, @TotalOfferedPrice, @AccessoryID, @PreDiscountPrice, @SailNumber, 
@SailNumberColourID, @AccessoryColourID, @NylonColourDetails, @FixedOfferPrice, @BRP, @ExRate, @FabricID, @NewBRP, 
@NotionalArea, @AccessAttachLineItem, @TopSurfaceID, @BotSurfaceID, @AccessoryItemID, @DKKPrice, @MarketPriceEuro, 
@MarketRetailPrice, @SeasonalPriceEuro, @SeasonalPriceCurr, @OptionTotal, @BillingPrice, @BillingDiscount, 
@BillingCurrency, @OEMSailListID, @OriginatingOEMSailListID, @COM, @DesignFile, @AxItem, @Notes, @NotArea, 
@DeliveryDate, @DepartmentID, @LocaleID, @AttachedToLineItem, @HasAttachment, @PreviousQuoteLineItemID, 
@SailMeasurementID, @OfferIncVat, @ConvertToOrder)


SET @QuoteLineItemID = (SELECT QuoteLineItemID FROM QuoteLineItem_Tbl WHERE CreateDate = @DateCreated)
		
		COMMIT TRANSACTION
	END TRY
	
	BEGIN CATCH
		IF @@ERROR <> 0
		ROLLBACK TRANSACTION
		SET @error=ERROR_MESSAGE()
		
	END CATCH
END